"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : sqlines工具
Case Name   : sqlines工具-s参数指定mysql，查看数据类型的转换情况
Create At   : 2022/01/28
@zou_jialiang0501115624
Description :
    1、sqlines安装
    2、创建测试脚本
    3、sqlines -s=mysql -t=opengauss -in={self.in_file} -out={self.out_file}
     -log={self.log_file}
    4、环境清理：卸载sqlines、删除对应文件
Expect      :
    1、sqlines安装成功
    2、脚本创建成功
    3、转换成功
        TINYINT 转为： SMALLINT
        MEDIUMINT 转为： INT
        DOUBLE 转为： DOUBLE PRECISION
        FLOAT 转为： DOUBLE PRECISION
        DATETIME 转为： TIMESTAMP
        TINYBLOB 转为： BYTEA
        BLOB 转为： BYTEA
        MEDIUNBLOB 转为： BYTEA
        LONGBLOB 转为： BYTEA
        TINYTEXT 转为： TEXT
        MEDIUMTEXT 转为： TEXT
        LONGTEXT 转为： TEXT
        BINARY 转为 BYTEA
        VARBINARY 转为 BYTEA
        其他的数据类型，原样输出
    4、环境清理成功
History     :
    modified: 2023-04-19 by @daiguatutu 优化unzip解压命令，避免命令执行超时失败
"""

import os
import unittest

from yat.test import Node
from yat.test import macro
from testcase.utils.Logger import Logger


class SqlinesTest(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----- {os.path.basename(__file__)} start-----')
        self.root = Node('PrimaryRoot')
        self.in_file = f'{os.path.basename(__file__)[:-3]}.sql'
        self.out_file = f'{os.path.basename(__file__)[:-3]}_out.sql'
        self.log_file = f'{os.path.basename(__file__)[:-3]}.log'
        self.file_name = 'openGauss-tools-sqlines-master.zip'
        self.unzip_dir = self.file_name.replace('.zip', '')
        self.file_download = os.path.join(macro.FTP_PLUGINS_PATH,
                                     'sqlines', self.file_name)
        self.local_realpath = os.path.join('/root',
                                      os.path.basename(__file__)[-19:-3])
        self.local_realpath_file = os.path.join(self.local_realpath,
                                                self.file_name)

    def test_main(self):
        step_txt = '----step1:安装sqlines，expect: sqlines安装成功----'
        self.log.info(step_txt)
        self.log.info('-------------------下载软件包--------------------')
        cmd = f'rm -rf {self.local_realpath};mkdir -p {self.local_realpath};' \
            f'wget -P {self.local_realpath} {self.file_download}'
        self.log.info(cmd)
        self.root.sh(cmd)
        cmd2 = f"ls {self.local_realpath_file}|awk -F '/' '{{{{print $NF}}}}'"
        self.log.info(cmd2)
        res = self.root.sh(cmd2).result()
        self.log.info(res)
        self.assertEqual(self.file_name, res, '文件下载失败：' + step_txt)

        self.log.info('-------------------解压软件包--------------------')
        cmd3 = f'timeout 30s unzip -o  -d {self.local_realpath}  ' \
            f'{self.local_realpath_file}'
        self.log.info(cmd3)
        res = self.root.sh(cmd3).result()
        self.log.info(res)
        cmd4 = f"ls -ld {os.path.join(self.local_realpath,self.unzip_dir)}" \
            f"|awk -F '/' '{{{{print $NF}}}}'"
        self.log.info(cmd4)
        res = self.root.sh(cmd4).result()
        self.log.info(res)
        self.assertEqual(self.unzip_dir, res, '文件解压失败：' + step_txt)

        self.log.info('-------------------安装sqlines--------------------')
        cmd = f'cd {os.path.join(self.local_realpath,self.unzip_dir)};' \
            f'sh build.sh -i;pwd'
        self.log.info(cmd)
        res = self.root.sh(cmd).result()
        self.log.info(res)
        self.assertIn('Install Sqlines successfully', res,
                      'Sqlines安装失败：' + step_txt)
        tmp_path = res.strip().splitlines()[-1].strip()
        mid_path = os.path.join(tmp_path, 'bin')
        file_name = 'sqlines'
        check_file = os.path.join(mid_path, file_name)
        cmd = f"ls -l {check_file}"
        self.log.info(cmd)
        res = self.root.sh(cmd).result()
        self.log.info(res)
        self.assertEqual(res[3], 'x', '文件没有执行权限' + step_txt)
        self.log.info('-----------------配置sqlines环境变量-----------------')
        self.source_path = f"export PATH={mid_path}:$PATH"

        step_txt = '----step2:创建测试脚本，expect: 脚本创建成功----'
        self.log.info(step_txt)
        cmd1 = f"""cat >{self.in_file} <<EOF
        CREATE TABLE t_Opengauss_Function_Sqlines_Case0053 ( 
                             column1 TINYINT,
                             column2 MEDIUMINT,
                             column3 DOUBLE,
                             column4 FLOAT,
                             column5 DATETIME,
                             column6 TINYBLOB,
                             column7 BLOB,
                             column8 MEDIUMBLOB,
                             column9 LONGBLOB,
                             column10 TINYTEXT,
                             column11 MEDIUMTEXT,
                             column12 LONGTEXT,
                             column13 SMALLINT,
                             column14 INT,
                             column15 BIGINT,
                             column16 NUMERIC,
                             column17 DECIMAL,
                             column18 BIT,
                             column19 DATE,
                             column20 TIMESTAMP,
                             column21 TIME,
                             column22 YEAR,
                             column23 CHAR,
                             column24 VARCHAR(1),
                             column25 BINARY(1),
                             column26 VARBINARY(1),
                             column27 TEXT,
                             column28 ENUM('str1','str2'),
                             column29 SET('str1','str2'),
                             column30 JSON,
                             column31 GEOMETRY,
                             column32 POINT,
                             column33 LINESTRING,
                             column34 POLYGON,
                             column35 MULTIPOINT,
                             column36 MULTILINESTRING,
                             column37 MULTIPOLYGON,
                             column38 GEOMETRYCOLLECTION
                  );
        """
        self.log.info(cmd1)
        self.root.sh(cmd1)
        cmd2 = f'ls {self.in_file}'
        self.log.info(cmd2)
        res = self.root.sh(cmd2).result()
        self.log.info(res)
        self.assertEqual(res, f'{self.in_file}', '执行失败' + step_txt)

        step_txt = '----step3:执行sqlines命令，expect: 转换成功，' \
                   'TINYINT 转为： SMALLINT ' \
                   'MEDIUMINT 转为： INT' \
                   'DOUBLE 转为： DOUBLE PRECISION ' \
                   'FLOAT 转为： DOUBLE PRECISION ' \
                   'DATETIME 转为： TIMESTAMP ' \
                   'TINYBLOB 转为： BYTEA ' \
                   'BLOB 转为： BYTEA ' \
                   'MEDIUNBLOB 转为： BYTEA ' \
                   'LONGBLOB 转为： BYTEA ' \
                   'TINYTEXT 转为： TEXT ' \
                   'MEDIUMTEXT 转为： TEXT ' \
                   'LONGTEXT 转为： TEXT ' \
                   'BINARY 转为 BYTEA ' \
                   'VARBINARY 转为 BYTEA' \
                   '其他的数据类型，原样输出'
        self.log.info(step_txt)
        cmd = f"{self.source_path};" \
            f"sqlines -s=mysql -t=opengauss -in={self.in_file}" \
            f" -out={self.out_file} -log={self.log_file}"
        self.log.info(cmd)
        res = self.root.sh(cmd).result()
        self.log.info(res)
        self.assertNotIn('error', res, '执行失败：')
        cmd2 = f'cat {self.out_file}'
        self.log.info(cmd2)
        res = self.root.sh(cmd2).result()
        self.log.info(res)
        check_list = ["", "column1 SMALLINT,", "column2 INT,",
                      "column3 DOUBLE PRECISION,", "column4 DOUBLE PRECISION,",
                      "column5 TIMESTAMP(0),", "column6 BYTEA,",
                      "column7 BYTEA,",
                      "column8 BYTEA,", "column9 BYTEA,", "column10 TEXT,",
                      "column11 TEXT,", "column12 TEXT,", "column13 SMALLINT,",
                      "column14 INT,", "column15 BIGINT,", "column16 NUMERIC,",
                      "column17 DECIMAL,", "column18 BIT,", "column19 DATE,",
                      "column20 TIMESTAMP,", "column21 TIME(0),",
                      "column22 YEAR,", "column23 CHAR,",
                      "column24 VARCHAR(1),", "column25 BYTEA,",
                      "column26 BYTEA,", "column27 TEXT,",
                      "column28 ENUM('str1','str2'),",
                      "column29 SET('str1','str2'),", "column30 JSON,",
                      "column31 GEOMETRY,", "column32 POINT,",
                      "column33 LINESTRING,", "column34 POLYGON,",
                      "column35 MULTIPOINT,", "column36 MULTILINESTRING,",
                      "column37 MULTIPOLYGON,", "column38 GEOMETRYCOLLECTION"]
        self.assertTrue(len(res.strip().splitlines()) >= 40, '行数不到40行')
        for i in range(1, 39):
            self.assertEqual(res.strip().splitlines()[i].strip(),
                             check_list[i], '执行失败：' + step_txt)

    def tearDown(self):
        step_txt = '----step4:环境清理，expect: 环境清理成功----'
        self.log.info(step_txt)
        self.log.info('--------------------卸载sqlines--------------------')
        cmd = f'cd {os.path.join(self.local_realpath,self.unzip_dir)};' \
            f'sh build.sh -m'
        self.log.info(cmd)
        res = self.root.sh(cmd).result()
        self.log.info(res)
        self.log.info('--------------------删除相关文件--------------------')
        cmd2 = f'rm -rf {self.local_realpath} ' \
            f'{self.in_file} {self.out_file} {self.log_file};' \
            f'if [ -d {self.local_realpath} ] ;' \
            f'then echo "exists" ;else echo "not exists"; fi '
        self.log.info(cmd2)
        res2 = self.root.sh(cmd2).result()
        self.assertIn('Uninstall Sqlines successfully', res,
                      'Sqlines卸载失败：' + step_txt)
        self.assertIn('not exists', res2, '删除文件失败' + cmd2)
        self.log.info(f'----- {os.path.basename(__file__)} end-----')